import pandas as pd

# 读取Excel文件
file_path = 'E:/M/MathModel/23国赛C题/ds/23年6月24到30.xlsx'
df = pd.read_excel(file_path)

# 计算每一行的利润
df['单品利润'] = (df['销售单价(元/千克)'] - df['批发价']) * df['销量(千克)']

# 按单品名称分组计算总利润
profit_by_item = df.groupby('单品名称')['单品利润'].sum().reset_index()

# 按利润降序排序
profit_by_item_sorted = profit_by_item.sort_values(by='单品利润', ascending=False)

# 添加连续排名列
profit_by_item_sorted.insert(0, '排名', range(1, len(profit_by_item_sorted) + 1))

# 重命名列名
profit_by_item_sorted.columns = ['排名', '单品名称', '总利润(元)']

print("单品利润排序结果：")
print(profit_by_item_sorted.to_string(index=False))
